Step 3: Relationships Explained — LinkedIn

Relationship Map Overview

The LinkedIn data model has 25+ distinct relationships spanning professional identity, networking, content, recruiting, and messaging domains.

1. Profile Relationships

users → work_experience (1:N)

A user can have multiple work experiences (positions) across their career.

users.user_id ←→ work_experience.user_id

Cardinality: One user, many positions. Average LinkedIn user has 3-5 experiences.

users → education (1:N)

A user can have multiple education entries.

users.user_id ←→ education.user_id

work_experience → companies (N:1)

Many work experiences can reference the same company.

work_experience.company_id ←→ companies.company_id

Note: company_id is nullable — not all employers have LinkedIn Company Pages. company_name text is always populated as fallback.

users → certifications (1:N)

users.user_id ←→ certifications.user_id

users → projects (1:N)

users.user_id ←→ projects.user_id

projects → work_experience (N:1 — optional)

A project can be optionally linked to a specific work experience entry.

projects.associated_experience_id ←→ work_experience.experience_id

2. Network / Social Graph Relationships

users ↔ users via connections (M:N — Bidirectional)

The core professional network graph. Connections require mutual acceptance.

users.user_id ←→ connections.requester_id
users.user_id ←→ connections.receiver_id

Implementation — finding 2nd degree connections:

-- Get 2nd-degree connections (friends of friends) with mutual count
WITH my_connections AS (
    SELECT CASE 
        WHEN requester_id = :user_id THEN receiver_id 
        ELSE requester_id 
    END AS connection_id
    FROM connections
    WHERE (requester_id = :user_id OR receiver_id = :user_id)
      AND status = 'accepted'
)
SELECT 
    u.user_id, u.first_name, u.last_name, u.headline,
    COUNT(mc.connection_id) AS mutual_connections
FROM connections c
JOIN my_connections mc ON (c.requester_id = mc.connection_id OR c.receiver_id = mc.connection_id)
JOIN users u ON u.user_id = CASE 
    WHEN c.requester_id = mc.connection_id THEN c.receiver_id 
    ELSE c.requester_id 
END
WHERE c.status = 'accepted'
  AND u.user_id != :user_id
  AND u.user_id NOT IN (SELECT connection_id FROM my_connections)
GROUP BY u.user_id, u.first_name, u.last_name, u.headline
ORDER BY mutual_connections DESC
LIMIT 20;

users → users via follows (M:N — Unidirectional)

Following is one-way — you see their public posts without connecting.

users.user_id ←→ follows.follower_id
users.user_id ←→ follows.followed_id

Key difference from connections: No acceptance required. Connecting auto-creates mutual follows. Unfollowing doesn't disconnect.

3. Skills & Endorsements Relationships

users ↔ skills via user_skills (M:N)

users.user_id ←→ user_skills.user_id ←→ user_skills.skill_id ←→ skills.skill_id

Cardinality: Max ~50 skills per user. 40,000+ skills in the taxonomy.

user_skills → endorsements (1:N)

Each skill on a user's profile can receive multiple endorsements from different connections.

user_skills.user_skill_id ←→ endorsements.user_skill_id

-- Get all skills for a user with endorsement counts and top endorsers
SELECT 
    s.name AS skill_name,
    us.endorsement_count,
    us.is_assessment_passed,
    (SELECT json_agg(json_build_object(
        'name', eu.first_name || ' ' || eu.last_name,
        'headline', eu.headline
    ))
    FROM endorsements e
    JOIN users eu ON e.endorsed_by = eu.user_id
    WHERE e.user_skill_id = us.user_skill_id
    LIMIT 5) AS top_endorsers
FROM user_skills us
JOIN skills s ON us.skill_id = s.skill_id
WHERE us.user_id = :user_id
ORDER BY us.endorsement_count DESC;

4. Recommendations Relationships

users → recommendations (1:N — as author)

users → recommendations (1:N — as recipient)

users.user_id ←→ recommendations.author_id     (recommendations I wrote)
users.user_id ←→ recommendations.recipient_id  (recommendations I received)

Bidirectional in nature: User A writes a recommendation FOR User B. Both can have multiple.

5. Company Relationships

companies → company_admins (1:N)

companies.company_id ←→ company_admins.company_id

Multiple admins with different roles can manage the same company page.

companies → company_followers (1:N)

companies.company_id ←→ company_followers.company_id

companies → job_postings (1:N)

companies.company_id ←→ job_postings.company_id

A company can have many active job postings.

companies → posts (1:N)

companies.company_id ←→ posts.company_id

Posts can be authored on behalf of a company (company page updates).

6. Content Relationships

users → posts (1:N)

users.user_id ←→ posts.author_id

posts → posts (Self-Referencing — Reposts)

posts.post_id ←→ posts.original_post_id

A repost references the original post while potentially adding commentary.

posts → post_reactions (1:N)

posts.post_id ←→ post_reactions.post_id

Constraint: One reaction per user per post.

posts → comments (1:N)

posts.post_id ←→ comments.post_id

comments → comments (Self-Referencing — Threading)

comments.comment_id ←→ comments.parent_comment_id

LinkedIn supports 2 levels: top-level comments and replies.

7. Jobs & Recruiting Relationships

job_postings → job_applications (1:N)

job_postings.job_id ←→ job_applications.job_id

Each job posting receives many applications.

users → job_applications (1:N)

users.user_id ←→ job_applications.applicant_id

A user can apply to many jobs, but only once per job.

-- Recruiter view: all applicants for a job, sorted by relevance
SELECT 
    u.first_name, u.last_name, u.headline, u.profile_photo_url,
    u.location, u.current_company,
    ja.status, ja.applied_at,
    -- Count matching skills between job requirements and applicant
    (SELECT COUNT(*) FROM user_skills us
     JOIN skills s ON us.skill_id = s.skill_id
     WHERE us.user_id = u.user_id
       AND s.name = ANY(string_to_array(jp.required_skills, ','))) AS matching_skills_count
FROM job_applications ja
JOIN users u ON ja.applicant_id = u.user_id
JOIN job_postings jp ON ja.job_id = jp.job_id
WHERE ja.job_id = :job_id
ORDER BY matching_skills_count DESC, ja.applied_at ASC;

8. Messaging Relationships

users → conversations (M:N via participants — implicit)

Conversations are created between users. For simplicity, participants are inferred from messages.

conversations → messages (1:N)

conversations.conversation_id ←→ messages.conversation_id

users → messages (1:N)

users.user_id ←→ messages.sender_id

9. Notifications Relationships

users → notifications (1:N)

users.user_id ←→ notifications.user_id

Polymorphic reference: entity_type + entity_id point to the relevant entity (post, job, profile, etc.).

Relationship Summary Table

This content is reserved for Premium Members.